#!/usr/bin/perl
#------------------------------------------------------------------------------
# Project  : Oracle to Postgresql converter
# Name     : ora2pg
# Author   : Gilles Darold, gilles _AT_ darold _DOT_ net
# Copyright: Copyright (c) 2000-2020 : Gilles Darold - All rights reserved -
# Function : Script used to convert Oracle Database to PostgreSQL
# Usage    : ora2pg configuration_file
#------------------------------------------------------------------------------
#
#        This program is free software: you can redistribute it and/or modify
#        it under the terms of the GNU General Public License as published by
#        the Free Software Foundation, either version 3 of the License, or
#        any later version.
#
#        This program is distributed in the hope that it will be useful,
#        but WITHOUT ANY WARRANTY; without even the implied warranty of
#        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#        GNU General Public License for more details.
#
#        You should have received a copy of the GNU General Public License
#        along with this program. If not, see < http://www.gnu.org/licenses/ >.
#
#------------------------------------------------------------------------------
use strict qw/vars/;

use Ora2Pg;
use Getopt::Long qw(:config no_ignore_case bundling);
use File::Spec qw/ tmpdir /;
use POSIX qw(locale_h sys_wait_h _exit);
setlocale(LC_NUMERIC, '');
setlocale(LC_ALL,     'C');

my $VERSION = '21.1';

$| = 1;

my $CONFIG_FILE = "/etc/ora2pg/ora2pg.conf";
my $FILE_CONF = '';
my $DEBUG = 0;
my $QUIET = 0;
my $HELP = 0;
my $LOGFILE = '';
my $EXPORT_TYPE = '';
my $OUTFILE = '';
my $OUTDIR = '';
my $SHOW_VER = 0;
my $PLSQL = '';
my $DSN = '';
my $DBUSER = '';
my $DBPWD = '';
my $SCHEMA = '';
my $TABLEONLY = '';
my $FORCEOWNER = '';
my $ORA_ENCODING = '';
my $PG_ENCODING = '';
my $INPUT_FILE = '';
my $EXCLUDE = '';
my $ALLOW = '';
my $VIEW_AS_TABLE = '';
my $ESTIMATE_COST;
my $COST_UNIT_VALUE;
my $DUMP_AS_HTML;
my $DUMP_AS_CSV;
my $DUMP_AS_SHEET;
my $THREAD_COUNT;
my $ORACLE_COPIES;
my $PARALLEL_TABLES;
my $DATA_LIMIT;
my $CREATE_PROJECT = '';
my $PROJECT_BASE = '.';
my $PRINT_HEADER = '';
my $HUMAN_DAY_LIMIT;
my $IS_MYSQL = 0;
my $AUDIT_USER = '';
my $PG_DSN = '';
my $PG_USER = '';
my $PG_PWD = '';
my $COUNT_ROWS = 0;
my $DATA_TYPE = '';
my $GRANT_OBJECT = '';
my $PG_SCHEMA = '';
my $NO_HEADER = 0;
my $ORACLE_SPEED = 0;
my $ORA2PG_SPEED = 0;
my $RELATIVE_PATH = 0;
my $AS_OF_SCN = 0;
my $OPENGAUSS = 0;
my $CREATE_OPENGAUSS_PROJECT = '';

my @SCHEMA_ARRAY  = qw( SEQUENCE TABLE PACKAGE VIEW GRANT TRIGGER FUNCTION PROCEDURE TABLESPACE PARTITION TYPE MVIEW DBLINK SYNONYM DIRECTORY );
my @EXTERNAL_ARRAY  = qw( KETTLE FDW );
my @REPORT_ARRAY  = qw( SHOW_VERSION SHOW_REPORT SHOW_SCHEMA SHOW_TABLE SHOW_COLUMN SHOW_ENCODING  );
my @TEST_ARRAY  = qw( TEST TEST_VIEW);
my @SOURCES_ARRAY = qw( PACKAGE VIEW TRIGGER FUNCTION PROCEDURE PARTITION TYPE MVIEW );
my @DATA_ARRAY    = qw( INSERT COPY );
my @CAPABILITIES  = qw( QUERY LOAD );

my @OPENGAUSS_SCHEMA_ARRAY = qw( SEQUENCE TABLE PACKAGE VIEW TRIGGER FUNCTION PROCEDURE TABLESPACE TYPE MVIEW DBLINK SYNONYM DIRECTORY );
my @OPENGAUSS_SOURCES_ARRAY = qw();

my @MYSQL_SCHEMA_ARRAY  = qw( TABLE VIEW GRANT TRIGGER FUNCTION PROCEDURE PARTITION DBLINK );
my @MYSQL_SOURCES_ARRAY = qw( VIEW TRIGGER FUNCTION PROCEDURE PARTITION );

my @GRANT_OBJECTS_ARRAY = ('USER','TABLE','VIEW','MATERIALIZED VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE BODY','TYPE','SYNONYM','DIRECTORY');

my $TMP_DIR      = File::Spec->tmpdir() || '/tmp';

# Collect command line arguments
GetOptions (
	'a|allow=s' => \$ALLOW,
        'b|basedir=s' => \$OUTDIR,
        'c|conf=s' => \$FILE_CONF,
        'd|debug!' => \$DEBUG,
        'D|data_type=s' => \$DATA_TYPE,
	'e|exclude=s' => \$EXCLUDE,
	'g|grant_object=s' => \$GRANT_OBJECT,
        'h|help!' => \$HELP,
	'i|input_file=s' => \$INPUT_FILE,
	'j|jobs=i' => \$THREAD_COUNT,
	'J|copies=i' => \$ORACLE_COPIES,
        'l|log=s' => \$LOGFILE,
	'L|limit=i' => \$DATA_LIMIT,
	'm|mysql!' => \$IS_MYSQL,
	'n|namespace=s' => \$SCHEMA,
	'N|pg_schema=s' => \$PG_SCHEMA,
        'o|out=s' => \$OUTFILE,
	'p|plsql!' => \$PLSQL,
	'P|parallel=i' =>\$PARALLEL_TABLES,
	'q|quiet!' => \$QUIET,
	'r|relative!' => \$RELATIVE_PATH,
	's|source=s' => \$DSN,
        't|type=s' => \$EXPORT_TYPE,
        'T|temp_dir=s' => \$TMP_DIR,
	'u|user=s' => \$DBUSER,
	'v|version!' => \$SHOW_VER,
	'w|password=s' => \$DBPWD,
	'x|xtable=s' => \$TABLEONLY, # Obsolete
	'forceowner=s' => \$FORCEOWNER,
	'nls_lang=s' => \$ORA_ENCODING,
	'client_encoding=s' => \$PG_ENCODING,
	'view_as_table=s' => \$VIEW_AS_TABLE,
	'estimate_cost!' =>\$ESTIMATE_COST,
	'cost_unit_value=i' =>\$COST_UNIT_VALUE,
	'dump_as_html!' =>\$DUMP_AS_HTML,
	'dump_as_csv!' =>\$DUMP_AS_CSV,
	'dump_as_sheet!' =>\$DUMP_AS_SHEET,
	'init_project=s' => \$CREATE_PROJECT,
	'init_project_for_opengauss=s' => \$CREATE_OPENGAUSS_PROJECT,
	'project_base=s' => \$PROJECT_BASE,
	'print_header!' => \$PRINT_HEADER,
	'human_days_limit=i' => \$HUMAN_DAY_LIMIT,
	'audit_user=s' => \$AUDIT_USER,
	'pg_dsn=s' => \$PG_DSN,
	'pg_user=s' => \$PG_USER,
	'pg_pwd=s' => \$PG_PWD,
	'count_rows!' => \$COUNT_ROWS,
	'no_header!' => \$NO_HEADER,
	'oracle_speed!' => \$ORACLE_SPEED,
	'ora2pg_speed!' => \$ORA2PG_SPEED,
);

# Check command line parameters
if ($SHOW_VER) {
	print "Ora2Pg v$VERSION\n";
	exit 0;
}
if ($HELP) {
	&usage();
}

if ($IS_MYSQL) {
	@SCHEMA_ARRAY = @MYSQL_SCHEMA_ARRAY;
	@SOURCES_ARRAY = @MYSQL_SOURCES_ARRAY;
	@EXTERNAL_ARRAY = ();
}

# Create project repository and useful stuff
if ($CREATE_PROJECT) {
	if (!-d "$PROJECT_BASE") {
		print "FATAL: Project base directory does not exists: $PROJECT_BASE\n";
		&usage();
	}
	print STDERR "Creating project $CREATE_PROJECT.\n";
	&create_project($CREATE_PROJECT, $PROJECT_BASE);
	exit 0;
}

if ($CREATE_OPENGAUSS_PROJECT) {
	if (!-d "$PROJECT_BASE") {
		print "FATAL: Project base directory does not exists: $PROJECT_BASE\n";
		&usage();
	}
	print STDERR "Creating project $CREATE_PROJECT.\n";
	&create_opengauss_project($CREATE_OPENGAUSS_PROJECT, $PROJECT_BASE);
	exit 0;
}

if ($GRANT_OBJECT && !grep(/^$GRANT_OBJECT$/, @GRANT_OBJECTS_ARRAY)) {
	print "FATAL: invalid grant object type in -g option. See GRAND_OBJECT configuration directive.\n";
	exit 1;
}

# Clean temporary files
unless(opendir(DIR, "$TMP_DIR")) {
	print "FATAL: can't opendir $TMP_DIR: $!\n";
	exit 1;
}
my @files = grep { $_ =~ /^tmp_ora2pg.*$/ } readdir(DIR);
closedir DIR;
foreach (@files) {
	if (not unlink("$TMP_DIR/$_\n")){
		print "FATAL: can not remove old temporary files $TMP_DIR/$_\n";
		exit 1;
	}
}

# Check configuration file
my $GOES_WITH_DEFAULT = 0;
if ($FILE_CONF && ! -e $FILE_CONF) {
	print "FATAL: can't find configuration file $FILE_CONF\n";
	&usage();
} elsif (!$FILE_CONF && ! -e $CONFIG_FILE) {
	# At least we need configuration to connect to Oracle
	if (!$DSN || (!$DBUSER && !$ENV{ORA2PG_USER}) || (!$DBPWD && !$ENV{ORA2PG_PASSWD})) {
		print "FATAL: can't find configuration file $CONFIG_FILE\n";
		&usage();
	}
	$CONFIG_FILE = '';
	$GOES_WITH_DEFAULT = 1;
}

push(@CAPABILITIES, @SCHEMA_ARRAY, @REPORT_ARRAY, @DATA_ARRAY, @EXTERNAL_ARRAY, @TEST_ARRAY);

# Validate export type
$EXPORT_TYPE = uc($EXPORT_TYPE);
$EXPORT_TYPE =~ s/DATA/COPY/;
foreach my $t (split(/[,;\s\t]+/, $EXPORT_TYPE)) {
	if ($t && !grep(/^$t$/, @CAPABILITIES)) {
		print "FATAL: Unknown export type: $t. Type supported: ", join(',', @CAPABILITIES), "\n";
		&usage();
	}
}

# Preserve barckward compatibility
if ($TABLEONLY) {
	warn "-x | --xtable is deprecated, use -a | --allow option instead.\n";
	if (!$ALLOW) {
		$ALLOW = $TABLEONLY;
	}
}

sub getout
{
        my $sig = shift;
        print STDERR "Received terminating signal ($sig).\n";
        $SIG{INT} = \&getout;
        $SIG{TERM} = \&getout;

	# Cleaning temporary files
	unless(opendir(DIR, "$TMP_DIR")) {
		print "FATAL: can't opendir $TMP_DIR: $!\n";
		exit 1;
	}
	my @files = grep { $_ =~ /^tmp_ora2pg.*$/ } readdir(DIR);
	closedir DIR;
	foreach (@files) {
		unlink("$TMP_DIR/$_\n");
	}

	exit 1;
}
$SIG{INT} = \&getout;
$SIG{TERM} = \&getout;

# Replace ; or space by comma in the user list
$AUDIT_USER =~ s/[;\s]+/,/g;

# Create an instance of the Ora2Pg perl module
my $schema = new Ora2Pg (
	config => $FILE_CONF || $CONFIG_FILE,
	type   => $EXPORT_TYPE,
	debug  => $DEBUG,
	logfile=> $LOGFILE,
	output => $OUTFILE,
	output_dir => $OUTDIR,
	plsql_pgsql => $PLSQL,
	datasource => $DSN,
        user => $DBUSER || $ENV{ORA2PG_USER},
        password => $DBPWD || $ENV{ORA2PG_PASSWD},
	schema => $SCHEMA,
	pg_schema => $PG_SCHEMA,
	force_owner => $FORCEOWNER,
        nls_lang => $ORA_ENCODING,
        client_encoding => $PG_ENCODING,
        input_file => $INPUT_FILE,
	quiet => $QUIET,
	exclude => $EXCLUDE,
	allow => $ALLOW,
	view_as_table => $VIEW_AS_TABLE,
	estimate_cost => $ESTIMATE_COST,
	cost_unit_value => $COST_UNIT_VALUE,
	dump_as_html => $DUMP_AS_HTML,
	dump_as_csv => $DUMP_AS_CSV,
	dump_as_sheet => $DUMP_AS_SHEET,
	thread_count => $THREAD_COUNT,
	oracle_copies => $ORACLE_COPIES,
	data_limit => $DATA_LIMIT,
	parallel_tables => $PARALLEL_TABLES,
	print_header => $PRINT_HEADER,
	human_days_limit => $HUMAN_DAY_LIMIT,
	is_mysql => $IS_MYSQL,
	audit_user => $AUDIT_USER,
	temp_dir => $TMP_DIR,
	pg_dsn => $PG_DSN,
	pg_user => $PG_USER,
	pg_pwd => $PG_PWD,
	count_rows => $COUNT_ROWS,
	data_type => $DATA_TYPE,
	grant_object => $GRANT_OBJECT,
	no_header => $NO_HEADER,
	oracle_speed => $ORACLE_SPEED,
	ora2pg_speed => $ORA2PG_SPEED,
	psql_relative_path => $RELATIVE_PATH,
);

# Look at configuration file if an input file is defined
if (!$INPUT_FILE && !$GOES_WITH_DEFAULT) {
	my $cf_file = $FILE_CONF || $CONFIG_FILE;
	my $fh = new IO::File;
	$fh->open($cf_file) or die "FATAL: can't read configuration file $cf_file, $!\n";
	while (my $l = <$fh>) {
		chomp($l);
		$l =~ s/\r//gs;
		$l =~ s/^\s*\#.*$//g;
		next if (!$l || ($l =~ /^\s+$/));
		$l =~ s/^\s*//; $l =~ s/\s*$//;
		my ($var, $val) = split(/\s+/, $l, 2);
		$var = uc($var);
		if ($var eq 'INPUT_FILE' && $val) {
			$INPUT_FILE = $val;
		}
	}
	$fh->close();
}

# Proceed to Oracle DB extraction following
# configuration file definitions.
if ( ($EXPORT_TYPE !~ /^SHOW_/i) && !$INPUT_FILE ) {
	$schema->export_schema();
}

# Check if error occurs during data export
unless(opendir(DIR, "$TMP_DIR")) {
	print "FATAL: can't opendir $TMP_DIR: $!\n";
	exit 1;
}
@files = grep { $_ =~ /^tmp_ora2pg.*$/ } readdir(DIR);
closedir DIR;
if ($#files >= 0) {
	print STDERR "\nWARNING: an error occurs during data export. Please check what's happen.\n\n";
	exit 2;
}

exit(0);

####
# Show usage
####
sub usage
{
	print qq{
Usage: ora2pg [-dhpqv --estimate_cost --dump_as_html] [--option value]

    -a | --allow str  : Comma separated list of objects to allow from export.
			Can be used with SHOW_COLUMN too.
    -b | --basedir dir: Set the default output directory, where files
			resulting from exports will be stored.
    -c | --conf file  : Set an alternate configuration file other than the
			default /etc/ora2pg/ora2pg.conf.
    -d | --debug      : Enable verbose output.
    -D | --data_type STR : Allow custom type replacement at command line.
    -e | --exclude str: Comma separated list of objects to exclude from export.
			Can be used with SHOW_COLUMN too.
    -h | --help       : Print this short help.
    -g | --grant_object type : Extract privilege from the given object type.
			See possible values with GRANT_OBJECT configuration.
    -i | --input file : File containing Oracle PL/SQL code to convert with
			no Oracle database connection initiated.
    -j | --jobs num   : Number of parallel process to send data to PostgreSQL.
    -J | --copies num : Number of parallel connections to extract data from Oracle.
    -l | --log file   : Set a log file. Default is stdout.
    -L | --limit num  : Number of tuples extracted from Oracle and stored in
			memory before writing, default: 10000.
    -m | --mysql      : Export a MySQL database instead of an Oracle schema.
    -n | --namespace schema : Set the Oracle schema to extract from.
    -N | --pg_schema schema : Set PostgreSQL's search_path.
    -o | --out file   : Set the path to the output file where SQL will
			be written. Default: output.sql in running directory.
    -p | --plsql      : Enable PLSQL to PLPGSQL code conversion.
    -P | --parallel num: Number of parallel tables to extract at the same time.
    -q | --quiet      : Disable progress bar.
    -r | --relative   : use \\ir instead of \\i in the psql scripts generated.
    -s | --source DSN : Allow to set the Oracle DBI datasource.
    -t | --type export: Set the export type. It will override the one
			given in the configuration file (TYPE).
    -T | --temp_dir DIR: Set a distinct temporary directory when two
                         or more ora2pg are run in parallel.
    -u | --user name  : Set the Oracle database connection user.
		        ORA2PG_USER environment variable can be used instead.
    -v | --version    : Show Ora2Pg Version and exit.
    -w | --password pwd : Set the password of the Oracle database user.
		        ORA2PG_PASSWD environment variable can be used instead.
    --forceowner      : Force ora2pg to set tables and sequences owner like in
		  Oracle database. If the value is set to a username this one
		  will be used as the objects owner. By default it's the user
		  used to connect to the Pg database that will be the owner.
    --nls_lang code: Set the Oracle NLS_LANG client encoding.
    --client_encoding code: Set the PostgreSQL client encoding.
    --view_as_table str: Comma separated list of views to export as table.
    --estimate_cost   : Activate the migration cost evaluation with SHOW_REPORT
    --cost_unit_value minutes: Number of minutes for a cost evaluation unit.
		  default: 5 minutes, corresponds to a migration conducted by a
		  PostgreSQL expert. Set it to 10 if this is your first migration.
   --dump_as_html     : Force ora2pg to dump report in HTML, used only with
                        SHOW_REPORT. Default is to dump report as simple text.
   --dump_as_csv      : As above but force ora2pg to dump report in CSV.
   --dump_as_sheet    : Report migration assessment with one CSV line per database.
   --init_project NAME: Initialise a typical ora2pg project tree. Top directory
                        will be created under project base dir.
   --project_base DIR : Define the base dir for ora2pg project trees. Default
                        is current directory.
   --print_header     : Used with --dump_as_sheet to print the CSV header
                        especially for the first run of ora2pg.
   --human_days_limit num : Set the number of human-days limit where the migration
                        assessment level switch from B to C. Default is set to
                        5 human-days.
   --audit_user LIST  : Comma separated list of usernames to filter queries in
                        the DBA_AUDIT_TRAIL table. Used only with SHOW_REPORT
                        and QUERY export type.
   --pg_dsn DSN       : Set the datasource to PostgreSQL for direct import.
   --pg_user name     : Set the PostgreSQL user to use.
   --pg_pwd password  : Set the PostgreSQL password to use.
   --count_rows       : Force ora2pg to perform a real row count in TEST action.
   --no_header        : Do not append Ora2Pg header to output file
   --oracle_speed     : Use to know at which speed Oracle is able to send
                        data. No data will be processed or written.
   --ora2pg_speed     : Use to know at which speed Ora2Pg is able to send
                        transformed data. Nothing will be written.

See full documentation at https://ora2pg.darold.net/ for more help or see
manpage with 'man ora2pg'.

ora2pg will return 0 on success, 1 on error. It will return 2 when a child
process has been interrupted and you've gotten the warning message:
    "WARNING: an error occurs during data export. Please check what's happen."
Most of the time this is an OOM issue, first try reducing DATA_LIMIT value.

};
	exit 1;

}

sub create_opengauss_project
{
	my ($create_project, $project_base) = @_;
	@SCHEMA_ARRAY = @OPENGAUSS_SCHEMA_ARRAY;
	@SOURCES_ARRAY = @OPENGAUSS_SOURCES_ARRAY;
	&create_project($create_project, $project_base);
}

####
# Create a generic project tree
####
sub create_project
{
	my ($create_project, $project_base) = @_;

	# Look at default configuration file to use
	my $conf_file = $CONFIG_FILE . '.dist';
	if ($FILE_CONF) {
		# Use file given in parameter
		$conf_file = $FILE_CONF;
	}
	if (!-f $conf_file || -z $conf_file) {
		print "FATAL: file $conf_file does not exists.\n";
		exit 1;
	}
	# Build entire project tree
	my $base_path = $project_base . '/' . $create_project;
	if (-e $base_path) {
		print "FATAL: project directory exists $base_path\n";
		exit 1;
	}
	mkdir("$base_path");
	print "$base_path/\n";
	mkdir("$base_path/schema");
	print "\tschema/\n";

	foreach my $exp (sort @SCHEMA_ARRAY ) {
		my $tpath = lc($exp);
		$tpath =~ s/y$/ie/;
		mkdir("$base_path/schema/" . $tpath . 's');
		print "\t\t" . $tpath . "s/\n";
	}
	mkdir("$base_path/sources");
	print "\tsources/\n";
	foreach my $exp (sort @SOURCES_ARRAY ) {
		my $tpath = lc($exp);
		$tpath =~ s/y$/ie/;
		mkdir("$base_path/sources/" . $tpath . 's');
		print "\t\t" . $tpath . "s/\n";
	}
	mkdir("$base_path/data");
	print "\tdata/\n";
	mkdir("$base_path/config");
	print "\tconfig/\n";
	mkdir("$base_path/reports");
	print "\treports/\n";
	print "\n";

	# Copy configuration file and transform it as a generic one
	print "Generating generic configuration file\n";
	if (open(IN, "$conf_file")) {
		my @cf = <IN>;
		close(IN);
		# Create a generic configuration file only if it has the .dist extension
		# otherwise use the configuration given at command line (-c option)
		if ($conf_file =~ /\.dist/) {
			&make_config_generic(\@cf);
		}
		unless(open(OUT, ">$base_path/config/ora2pg.conf")) {
			print "FATAL: can't write to file $base_path/config/ora2pg.conf\n";
			exit 1;
		}
		print OUT @cf;
		close(OUT);
	} else {
		print "FATAL: can not read file $conf_file, $!.\n";
		exit 1;
	}

	# Generate shell script to execute all export
	print "Creating script export_schema.sh to automate all exports.\n";
	unless(open(OUT, "> $base_path/export_schema.sh")) {
		print "FATAL: Can't write to file $base_path/export_schema.sh\n";
		exit 1;
	}
	print OUT qq{#!/bin/sh
#-------------------------------------------------------------------------------
#
# Generated by Ora2Pg, the Oracle database Schema converter, version $VERSION
#
#-------------------------------------------------------------------------------
};
	print OUT "EXPORT_TYPE=\"", join(' ', @SCHEMA_ARRAY), "\"\n";
	print OUT "SOURCE_TYPE=\"", join(' ', @SOURCES_ARRAY), "\"\n";
	print OUT "namespace=\".\"\n";
	print OUT qq{
ora2pg -t SHOW_TABLE -c \$namespace/config/ora2pg.conf > \$namespace/reports/tables.txt
ora2pg -t SHOW_COLUMN -c \$namespace/config/ora2pg.conf > \$namespace/reports/columns.txt
ora2pg -t SHOW_REPORT -c \$namespace/config/ora2pg.conf --dump_as_html --estimate_cost > \$namespace/reports/report.html

for etype in \$(echo \$EXPORT_TYPE | tr " " "\\n")
do
        ltype=`echo \$etype | tr '[:upper:]' '[:lower:]'`
        ltype=`echo \$ltype | sed 's/y\$/ie/'`
        echo "Running: ora2pg -p -t \$etype -o \$ltype.sql -b \$namespace/schema/\$\{ltype\}s -c \$namespace/config/ora2pg.conf"
        ora2pg -p -t \$etype -o \$ltype.sql -b \$namespace/schema/\$\{ltype\}s -c \$namespace/config/ora2pg.conf
	ret=`grep "Nothing found" \$namespace/schema/\$\{ltype\}s/\$ltype.sql 2> /dev/null`
	if [ ! -z "\$ret" ]; then
		rm \$namespace/schema/\$\{ltype\}s/\$ltype.sql
	fi
done

for etype in \$(echo \$SOURCE_TYPE | tr " " "\\n")
do
        ltype=`echo \$etype | tr '[:upper:]' '[:lower:]'`
        ltype=`echo \$ltype | sed 's/y\$/ie/'`
        echo "Running: ora2pg -t \$etype -o \$ltype.sql -b \$namespace/sources/\$\{ltype\}s -c \$namespace/config/ora2pg.conf"
        ora2pg -t \$etype -o \$ltype.sql -b \$namespace/sources/\$\{ltype\}s -c \$namespace/config/ora2pg.conf
	ret=`grep "Nothing found" \$namespace/sources/\$\{ltype\}s/\$ltype.sql 2> /dev/null`
	if [ ! -z "\$ret" ]; then
		rm \$namespace/sources/\$\{ltype\}s/\$ltype.sql
	fi
done

echo
echo
echo "To extract data use the following command:"
echo
echo "ora2pg -t COPY -o data.sql -b \$namespace/data -c \$namespace/config/ora2pg.conf"
echo

exit 0
};
	close(OUT);
	chmod(0700, "$base_path/export_schema.sh");


	# Generate shell script to execute all import
	print "Creating script import_all.sh to automate all imports.\n";
	my $exportype = "EXPORT_TYPE=\"TYPE " . join(' ', grep( !/^TYPE$/, @SCHEMA_ARRAY)) . "\"\n";
	unless(open(OUT, "> $base_path/import_all.sh")) {
		print "FATAL: Can't write to file $base_path/import_all.sh\n";
		exit 1;
	}
	while (my $l = <DATA>) {
		$l =~ s/^EXPORT_TYPE=.*/$exportype/s;
		$l =~ s/ORA2PG_VERSION/$VERSION/s;
		print OUT $l;
	}
	close(OUT);
	chmod(0700, "$base_path/import_all.sh");
}

####
# Set a generic configuration
####
sub make_config_generic
{
	my $conf_arr = shift;

	chomp(@$conf_arr);

	my $schema = 'CHANGE_THIS_SCHEMA_NAME';
	$schema = $SCHEMA if ($SCHEMA);
	for (my $i = 0; $i <= $#{$conf_arr}; $i++) {
		if ($IS_MYSQL) {
			$conf_arr->[$i] =~ s/^# Set Oracle database/# Set MySQL database/;
			$conf_arr->[$i] =~ s/^(ORACLE_DSN.*dbi):Oracle:(.*);sid=SIDNAME/$1:mysql:$2;database=dbname/;
			$conf_arr->[$i] =~ s/CHANGE_THIS_SCHEMA_NAME/CHANGE_THIS_DB_NAME/;
			$conf_arr->[$i] =~ s/#REPLACE_ZERO_DATE.*/REPLACE_ZERO_DATE\t-INFINITY/;
		} elsif ($ENV{ORACLE_HOME}) {
			$conf_arr->[$i] =~ s/^ORACLE_HOME.*/ORACLE_HOME\t$ENV{ORACLE_HOME}/;
		}
		$conf_arr->[$i] =~ s/^USER_GRANTS.*0/USER_GRANTS\t1/;
		$conf_arr->[$i] =~ s/^#SCHEMA.*SCHEMA_NAME/SCHEMA\t$schema/;
		$conf_arr->[$i] =~ s/^(BINMODE.*)/#$1/;
		$conf_arr->[$i] =~ s/^PLSQL_PGSQL.*1/PLSQL_PGSQL\t0/;
		$conf_arr->[$i] =~ s/^FILE_PER_CONSTRAINT.*0/FILE_PER_CONSTRAINT\t1/;
		$conf_arr->[$i] =~ s/^FILE_PER_INDEX.*0/FILE_PER_INDEX\t1/;
		$conf_arr->[$i] =~ s/^FILE_PER_FKEYS.*0/FILE_PER_FKEYS\t1/;
		$conf_arr->[$i] =~ s/^FILE_PER_TABLE.*0/FILE_PER_TABLE\t1/;
		$conf_arr->[$i] =~ s/^FILE_PER_FUNCTION.*0/FILE_PER_FUNCTION\t1/;
		$conf_arr->[$i] =~ s/^TRUNCATE_TABLE.*0/TRUNCATE_TABLE\t1/;
		$conf_arr->[$i] =~ s/^DISABLE_SEQUENCE.*0/DISABLE_SEQUENCE\t1/;
		$conf_arr->[$i] =~ s/^DISABLE_TRIGGERS.*0/DISABLE_TRIGGERS\t1/;
		$conf_arr->[$i] =~ s/^(CLIENT_ENCODING.*)/#$1/;
		$conf_arr->[$i] =~ s/^(NLS_LANG.*)/#$1/;
		$conf_arr->[$i] =~ s/^#LONGREADLEN.*1047552/LONGREADLEN\t1047552/;
		$conf_arr->[$i] =~ s/^AUTODETECT_SPATIAL_TYPE.*0/AUTODETECT_SPATIAL_TYPE\t1/;
		$conf_arr->[$i] =~ s/^NO_LOB_LOCATOR.*/NO_LOB_LOCATOR\t0/;
		$conf_arr->[$i] =~ s/^USE_LOB_LOCATOR.*/USE_LOB_LOCATOR\t1/;
		$conf_arr->[$i] =~ s/^FTS_INDEX_ONLY.*0/FTS_INDEX_ONLY\t1/;
		$conf_arr->[$i] =~ s/^DISABLE_UNLOGGED.*0/DISABLE_UNLOGGED\t1/;
		$conf_arr->[$i] =~ s/^EMPTY_LOB_NULL.*0/EMPTY_LOB_NULL\t1/;
		if ($DSN) {
			$conf_arr->[$i] =~ s/^ORACLE_DSN.*/ORACLE_DSN\t$DSN/;
		}
		if ($DBUSER) {
			$conf_arr->[$i] =~ s/^ORACLE_USER.*/ORACLE_USER\t$DBUSER/;
		}
		if ($DBPWD) {
			$conf_arr->[$i] =~ s/^ORACLE_PWD.*/ORACLE_PWD\t$DBPWD/;
		}
	}
	map { s/$/\n/; } @$conf_arr;
}

__DATA__
#!/bin/sh
#-------------------------------------------------------------------------------
#
# Script used to load exported sql files into PostgreSQL in practical manner
# allowing you to chain and automatically import schema and data.
#
# Generated by Ora2Pg, the Oracle database Schema converter, version ORA2PG_VERSION
#
#-------------------------------------------------------------------------------

EXPORT_TYPE="TYPE,TABLE,PARTITION,VIEW,MVIEW,FUNCTION,PROCEDURE,SEQUENCE,TRIGGER,SYNONYM,DIRECTORY,DBLINK"
AUTORUN=0
NAMESPACE=.
NO_CONSTRAINTS=0
IMPORT_INDEXES_AFTER=0
DEBUG=0
IMPORT_SCHEMA=0
IMPORT_DATA=0
IMPORT_CONSTRAINTS=0
NO_DBCHECK=0
OPENGAUSS=0


# Message functions
die() {
    echo "ERROR: $1" 1>&2
    exit 1
}

usage() {
    echo "usage: `basename $0` [options]"
    echo ""
    echo "Script used to load exported sql files into PostgreSQL in practical manner"
    echo "allowing you to chain and automatically import schema and data."
    echo ""
    echo "options:"
    echo "    -a             import data only"
    echo "    -b filename    SQL script to execute just after table creation to fix database schema"
    echo "    -d dbname      database name for import"
    echo "    -D             enable debug mode, will only show what will be done"
    echo "    -e encoding    database encoding to use at creation (default: UTF8)"
    echo "    -f             force no check of user and database existing and do not try to create them"
    echo "    -h hostname    hostname of the PostgreSQL server (default: unix socket)"
    echo "    -i             only load indexes, constraints and triggers"
    echo "    -I             do not try to load indexes, constraints and triggers"
    echo "    -j cores       number of connection to use to import data or indexes into PostgreSQL"
    echo "    -n schema      comma separated list of schema to create"
    echo "    -o username    owner of the database to create"
    echo "    -w password    password of specified database owner"
    echo "    -p port        listening port of the PostgreSQL server (default: 5432)"
    echo "    -P cores       number of tables to process at same time for data import"
    echo "    -s             import schema only, do not try to import data"
    echo "    -t export      comma separated list of export type to import (same as ora2pg)"
    echo "    -U username    username to connect to PostgreSQL (default: peer username)"
    echo "    -x             import indexes and constraints after data"
    echo "    -y             reply Yes to all questions for automatic import"
    echo "    -g             specify that the destination database is openGauss"
    echo
    echo "    -?             print help"
    echo
    exit $1
}

# Function to emulate Perl prompt function
confirm () {

    msg=$1
    if [ "$AUTORUN" != "0" ]; then
	true
    else
	    if [ -z "$msg" ]; then
		msg="Are you sure? [y/N/q]"
	    fi
	    # call with a prompt string or use a default
	    read -r -p "${msg} [y/N/q] " response
	    case $response in
		[yY][eE][sS]|[yY])
		    true
		    ;;
		[qQ][uU][iI][tT]|[qQ])
		    exit
		    ;;
		*)
		    false
		    ;;
	    esac
    fi
}

# Function used to import constraints and indexes
import_constraints () {
	if [ -r "$NAMESPACE/schema/tables/INDEXES_table.sql" ]; then
		if confirm "Would you like to import indexes from $NAMESPACE/schema/tables/INDEXES_table.sql?" ; then
			if [ -z "$IMPORT_JOBS" ]; then
				echo "Running: $sql$DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $NAMESPACE/schema/tables/INDEXES_table.sql"
				if [ $DEBUG -eq 0 ]; then
					$sql$DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $NAMESPACE/schema/tables/INDEXES_table.sql
					if [ $? -ne 0 ]; then
						die "can not import indexes."
					fi
				fi
			else
				echo "Running: ora2pg -c config/ora2pg.conf -t LOAD -i $NAMESPACE/schema/tables/INDEXES_table.sql"
				if [ $DEBUG -eq 0 ]; then
					ora2pg$IMPORT_JOBS -c config/ora2pg.conf -t LOAD -i $NAMESPACE/schema/tables/INDEXES_table.sql
					if [ $? -ne 0 ]; then
						die "can not import indexes."
					fi
				fi
			fi
		fi
	fi

	if [ -r "$NAMESPACE/schema/tables/CONSTRAINTS_table.sql" ]; then
		if confirm "Would you like to import constraints from $NAMESPACE/schema/tables/CONSTRAINTS_table.sql?" ; then
			if [ -z "$IMPORT_JOBS" ]; then
				echo "Running: $sql$DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $NAMESPACE/schema/tables/CONSTRAINTS_table.sql"
				if [ $DEBUG -eq 0 ]; then
					$sql$DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $NAMESPACE/schema/tables/CONSTRAINTS_table.sql
					if [ $? -ne 0 ]; then
						die "can not import constraints."
					fi
				fi
			else
				echo "Running: ora2pg$IMPORT_JOBS -c config/ora2pg.conf -t LOAD -i $NAMESPACE/schema/tables/CONSTRAINTS_table.sql"
				if [ $DEBUG -eq 0 ]; then
					ora2pg$IMPORT_JOBS -c config/ora2pg.conf -t LOAD -i $NAMESPACE/schema/tables/CONSTRAINTS_table.sql
					if [ $? -ne 0 ]; then
						die "can not import constraints."
					fi
				fi
			fi
		fi
	fi

	if [ -r "$NAMESPACE/schema/tables/FKEYS_table.sql" ]; then
		if confirm "Would you like to import foreign keys from $NAMESPACE/schema/tables/FKEYS_table.sql?" ; then
			if [ -z "$IMPORT_JOBS" ]; then
				echo "Running: $sql$DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $NAMESPACE/schema/tables/FKEYS_table.sql"
				if [ $DEBUG -eq 0 ]; then
					$sql$DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $NAMESPACE/schema/tables/FKEYS_table.sql
					if [ $? -ne 0 ]; then
						die "can not import foreign keys."
					fi
				fi
			else
				echo "Running: ora2pg$IMPORT_JOBS -c config/ora2pg.conf -t LOAD -i $NAMESPACE/schema/tables/FKEYS_table.sql"
				if [ $DEBUG -eq 0 ]; then
					ora2pg$IMPORT_JOBS -c config/ora2pg.conf -t LOAD -i $NAMESPACE/schema/tables/FKEYS_table.sql
					if [ $? -ne 0 ]; then
						die "can not import foreign keys."
					fi
				fi
			fi
		fi
	fi

	if [ $NO_CONSTRAINTS -eq 1 ] && [ -r "$NAMESPACE/schema/triggers/trigger.sql" ]; then
		if confirm "Would you like to import TRIGGER from $NAMESPACE/schema/triggers/trigger.sql?" ; then
			echo "Running: $sql --single-transaction $DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $NAMESPACE/schema/triggers/trigger.sql"
			if [ $DEBUG -eq 0 ]; then
				$sql --single-transaction $DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $NAMESPACE/schema/triggers/trigger.sql
				if [ $? -ne 0 ]; then
					die "an error occurs when importing file $NAMESPACE/schema/triggers/trigger.sql."
				fi
			fi
		fi
	fi
}

# Command line options
while getopts "b:d:e:h:j:l:n:o:w:p:P:t:U:aDfiIsyxg?"  opt; do
    case "$opt" in
	a) IMPORT_DATA=1;;
	b) SQL_POST_SCRIPT=$OPTARG;;
        d) DB_NAME=$OPTARG;;
        D) DEBUG=1;;
        e) DB_ENCODING=" -E $OPTARG";;
	f) NO_DBCHECK=1;;
        h) DB_HOST=" -h $OPTARG";;
        i) IMPORT_CONSTRAINTS=1;;
        I) NO_CONSTRAINTS=1;;
        j) IMPORT_JOBS=" -j $OPTARG";;
        n) DB_SCHEMA=$OPTARG;;
        o) DB_OWNER=$OPTARG;;
        w) DB_OWNER_PASSWORD=$OPTARG;;
        p) DB_PORT=" -p $OPTARG";;
        P) PARALLEL_TABLES=" -P $OPTARG";;
        s) IMPORT_SCHEMA=1;;
        t) EXPORT_TYPE=$OPTARG;;
        U) DB_USER=" -U $OPTARG";;
	x) IMPORT_INDEXES_AFTER=1;;
        y) AUTORUN=1;;
        g) OPENGAUSS=1;;
        "?") usage 1;;
        *) die "Unknown error while processing options";;
    esac
done

# Check if post tables import SQL script is readable
if [ ! -z "$SQL_POST_SCRIPT" ]; then
	if [ ! -r "$SQL_POST_SCRIPT" ]; then
		die "the SQL script $SQL_POST_SCRIPT is not readable."
	fi
fi

# A database name is mandatory
if [ -z "$DB_NAME" ]; then
	die "you must give a PostgreSQL database name (see -d option)."
fi

# A database owner is mandatory
if [ -z "$DB_OWNER" ]; then
	die "you must give a username to be used as owner of database (see -o option)."
fi

# Check if the project directory is readable
if [ ! -r "$NAMESPACE/schema/tables/table.sql" ]; then
	die "project directory '$NAMESPACE' is not valid or is not readable."
fi

# If constraints and indexes files are present propose to import these object
if [ $IMPORT_CONSTRAINTS -eq 1 ]; then
	if confirm "Would you like to load indexes, constraints and triggers?" ; then
		import_constraints
	fi
	exit 0
fi

if [ "$OPENGAUSS" != "0" ]; then
	sql="gsql"
	password="-W $DB_OWNER_PASSWORD"
else
	sql="psql"
	password=""
fi

# When a PostgreSQL schema list is provided, create them
if [ $IMPORT_DATA -eq 0 ]; then
	is_superuser='f'
	if [ $NO_DBCHECK  -eq 0 ]; then
		# Create owner user
		user_exists=`$sql -d postgres$DB_HOST$DB_PORT$DB_USER -Atc "select usename from pg_user where usename='$DB_OWNER';"`
		is_superuser=`$sql -d postgres$DB_HOST$DB_PORT$DB_USER -Atc "select usesuper from pg_user where usename='$DB_OWNER';"`;
		if [ "a$user_exists" = "a" ]; then
			if confirm "Would you like to create the owner of the database $DB_OWNER?" ; then
				if [ "$OPENGAUSS" != "0" ]; then
					echo "Running: gsql$DB_HOST$DB_PORT$DB_USER -d postgres -c \"CREATE USER $DB_OWNER IDENTIFIED BY '$DB_OWNER_PASSWORD';\""
				else
					echo "Running: createuser$DB_HOST$DB_PORT$DB_USER --no-superuser --no-createrole --no-createdb $DB_OWNER"
				fi
				if [ $DEBUG -eq 0 ]; then
					if [ "$OPENGAUSS" != "0" ]; then
						gsql$DB_HOST$DB_PORT$DB_USER -d postgres -c "CREATE USER $DB_OWNER IDENTIFIED BY '$DB_OWNER_PASSWORD';"
					else
						createuser$DB_HOST$DB_PORT$DB_USER --no-superuser --no-createrole --no-createdb $DB_OWNER
					fi
					if [ $? -ne 0 ]; then
						die "can not create user $DB_OWNER."
					fi
				fi
			fi
		else
			echo "Database owner $DB_OWNER already exists, skipping creation."
		fi

		# Create database if required
		if [ "a$DB_ENCODING" = "a" ]; then
			DB_ENCODING=" -E UTF8"
		fi
		db_exists=`$sql -d postgres$DB_HOST$DB_PORT$DB_USER -Atc "select datname from pg_database where datname='$DB_NAME';"`
		if [ "a$db_exists" = "a" ]; then
			if confirm "Would you like to create the database $DB_NAME?" ; then
				if [ "$OPENGAUSS" != "0" ]; then
					echo "Running: gsql$DB_HOST$DB_PORT$DB_USER -d postgres -c \"CREATE DATABASE $DB_NAME OWNER $DB_OWNER ENCODING '${DB_ENCODING:4}';\""
				else
					echo "Running: createdb$DB_HOST$DB_PORT$DB_USER$DB_ENCODING --owner $DB_OWNER $DB_NAME"
				fi
				if [ $DEBUG -eq 0 ]; then
					if [ "$OPENGAUSS" != "0" ]; then
						gsql$DB_HOST$DB_PORT$DB_USER -d postgres -c "CREATE DATABASE $DB_NAME OWNER $DB_OWNER ENCODING '${DB_ENCODING:4}';"
					else
						createdb$DB_HOST$DB_PORT$DB_USER$DB_ENCODING --owner $DB_OWNER $DB_NAME
					fi
					if [ $? -ne 0 ]; then
						die "can not create database $DB_NAME."
					fi
				fi
			fi
		else
			if confirm "Would you like to drop the database $DB_NAME before recreate it?" ; then
				if [ "$OPENGAUSS" != "0" ]; then
					echo "Running: gsql$DB_HOST$DB_PORT$DB_USER -d postgres -c \"DROP DATABASE $DB_NAME;\""
				else
					echo "Running: dropdb$DB_HOST$DB_PORT$DB_USER $DB_NAME"
				fi
				if [ $DEBUG -eq 0 ]; then
					if [ "$OPENGAUSS" != "0" ]; then
						gsql$DB_HOST$DB_PORT$DB_USER -d postgres -c "DROP DATABASE $DB_NAME;"
					else
						dropdb$DB_HOST$DB_PORT$DB_USER $DB_NAME
					fi
					if [ $? -ne 0 ]; then
						die "can not drop database $DB_NAME."
					fi
				fi
				if [ "$OPENGAUSS" != "0" ]; then
					echo "Running: gsql$DB_HOST$DB_PORT$DB_USER -d postgres -c \"CREATE DATABASE $DB_NAME OWNER $DB_OWNER ENCODING '${DB_ENCODING:4}';\""
				else
					echo "Running: createdb$DB_HOST$DB_PORT$DB_USER$DB_ENCODING --owner $DB_OWNER $DB_NAME"
				fi
				if [ $DEBUG -eq 0 ]; then
					if ["$OPENGAUSS" != "0" ]; then
						gsql$DB_HOST$DB_PORT$DB_USER -d postgres -c "CREATE DATABASE $DB_NAME OWNER $DB_OWNER ENCODING '${DB_ENCODING:4}';"
					else
						createdb$DB_HOST$DB_PORT$DB_USER$DB_ENCODING --owner $DB_OWNER $DB_NAME
					fi
					if [ $? -ne 0 ]; then
						die "can not create database $DB_NAME."
					fi
				fi
			fi
		fi
	fi

	# When schema list is provided, create them
	if [ "a$DB_SCHEMA" != "a" ]; then
		nspace_list=''
		for enspace in $(echo $DB_SCHEMA | tr "," "\n")
		do
			lnspace=`echo $enspace | tr '[:upper:]' '[:lower:]'`
			if confirm "Would you like to create schema $lnspace in database $DB_NAME?" ; then
				echo "Running: $sql$DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -c \"CREATE SCHEMA $lnspace;\""
				if [ $DEBUG -eq 0 ]; then
					$sql$DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -c "CREATE SCHEMA $lnspace;"
					if [ $? -ne 0 ]; then
						die "can not create schema $DB_SCHEMA."
					fi
				fi
				nspace_list="$nspace_list$lnspace,"
			fi
		done
		# Change search path of the owner
		if [ "a$nspace_list" != "a" ]; then
			if confirm "Would you like to change search_path of the database owner?" ; then
				echo "Running: $sql$DB_HOST$DB_PORT$DB_USER -d $DB_NAME -c \"ALTER ROLE $DB_OWNER SET search_path TO ${nspace_list}public;\""
				if [ $DEBUG -eq 0 ]; then
					$sql$DB_HOST$DB_PORT$DB_USER -d $DB_NAME -c "ALTER ROLE $DB_OWNER SET search_path TO ${nspace_list}public;"
					if [ $? -ne 0 ]; then
						die "can not change search_path."
					fi
				fi
			fi
		fi
	else
		if [ "$OPENGAUSS" != "0" ] && [ $NO_DBCHECK -eq 0 ]; then
			echo "Running: gsql$DB_HOST$DB_PORT$DB_USER -d $DB_NAME -c \"grant create on schema public to $DB_OWNER;\""
			gsql$DB_HOST$DB_PORT$DB_USER -d $DB_NAME -c "grant create on schema public to $DB_OWNER;"
		fi
	fi

	# Then import all files from project directory
	for etype in $(echo $EXPORT_TYPE | tr "," "\n")
	do

		if [ $NO_CONSTRAINTS -eq 1 ] && [ $etype = "TRIGGER" ]; then
			continue
		fi

		if [ $etype = "GRANT" ] || [ $etype = "TABLESPACE" ]; then
			continue
		fi

		ltype=`echo $etype | tr '[:upper:]' '[:lower:]'`
		ltype=`echo $ltype | sed 's/y$/ie/'`
		if [ -r "$NAMESPACE/schema/${ltype}s/$ltype.sql" ]; then
			if confirm "Would you like to import $etype from $NAMESPACE/schema/${ltype}s/$ltype.sql?" ; then
				echo "Running: $sql --single-transaction $DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $NAMESPACE/schema/${ltype}s/$ltype.sql"
				if [ $DEBUG -eq 0 ]; then
					$sql --single-transaction $DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $NAMESPACE/schema/${ltype}s/$ltype.sql
					if [ $? -ne 0 ]; then
						die "an error occurs when importing file $NAMESPACE/schema/${ltype}s/$ltype.sql."
					fi
				fi
			fi
		fi
		if [ ! -z "$SQL_POST_SCRIPT" ] && [ $etype = "TABLE" ]; then
			if confirm "Would you like to execute SQL script $SQL_POST_SCRIPT?" ; then
				echo "Running: $sql --single-transaction $DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $SQL_POST_SCRIPT"
				if [ $DEBUG -eq 0 ]; then
					$sql --single-transaction $DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $SQL_POST_SCRIPT
					if [ $? -ne 0 ]; then
						die "an error occurs when importing file $SQL_POST_SCRIPT."
					fi
				fi
			fi
		fi
	done

	# If constraints and indexes files are present propose to import these object
	if [ $NO_CONSTRAINTS -eq 0 ] && [ $IMPORT_INDEXES_AFTER -eq 0 ]; then
		if confirm "Would you like to process indexes and constraints before loading data?" ; then
			IMPORT_INDEXES_AFTER=0
			import_constraints
		else
			IMPORT_INDEXES_AFTER=1
		fi
	fi

	# When the database owner is not superuser use postgres instead
	q_user='postgres'
	if [ "$is_superuser" = "t" ]; then
		q_user=$DB_OWNER
	fi
	# use current system user instead if openGauss
	if [ $OPENGAUSS != '0' ]; then
		q_user=$USER
	fi

	# Import objects that need superuser privilege: GRANT and TABLESPACE
	if [ -r "$NAMESPACE/schema/grants/grant.sql" ]; then
		if confirm "Would you like to import GRANT from $NAMESPACE/schema/grants/grant.sql?" ; then
			echo "Running: $sql $DB_HOST$DB_PORT -U $q_user -d $DB_NAME -f $NAMESPACE/schema/grants/grant.sql"
			if [ $DEBUG -eq 0 ]; then
				$sql $DB_HOST$DB_PORT -U $q_user -d $DB_NAME -f $NAMESPACE/schema/grants/grant.sql
				if [ $? -ne 0 ]; then
					die "an error occurs when importing file $NAMESPACE/schema/grants/grant.sql."
				fi
			fi
		fi
	fi
	if [ -r "$NAMESPACE/schema/tablespaces/tablespace.sql" ]; then
		if confirm "Would you like to import TABLESPACE from $NAMESPACE/schema/tablespaces/tablespace.sql?" ; then
			echo "Running: $sql $DB_HOST$DB_PORT -U $q_user -d $DB_NAME -f $NAMESPACE/schema/tablespaces/tablespace.sql"
			if [ $DEBUG -eq 0 ]; then
				$sql $DB_HOST$DB_PORT -U $q_user -d $DB_NAME -f $NAMESPACE/schema/tablespaces/tablespace.sql
				if [ $? -ne 0 ]; then
					die "an error occurs when importing file $NAMESPACE/schema/tablespaces/tablespace.sql."
				fi
			fi
		fi
	fi
fi


# Check if we must just import schema or proceed to data import too
if [ $IMPORT_SCHEMA -eq 0 ]; then
	# set the PostgreSQL datasource
	pgdsn_defined=`grep "^PG_DSN" config/ora2pg.conf | sed 's/.*dbi:Pg/dbi:Pg/'`
	if [ "a$pgdsn_defined" = "a" ]; then
		if [ "a$DB_HOST" != "a" ]; then
			pgdsn_defined="dbi:Pg:dbname=$DB_NAME;host=$DB_HOST"
		else
      #default to unix socket
      pgdsn_defined="dbi:Pg:dbname=$DB_NAME;"
    fi
		if [ "a$DB_PORT" != "a" ]; then
			pgdsn_defined="$pgdsn_defined;port=$DB_PORT"
		else
			pgdsn_defined="$pgdsn_defined;port=5432"
		fi
	fi

	# remove command line option from the DSN string
	pgdsn_defined=`echo "$pgdsn_defined" | sed 's/ -. //g'`

	# If data file is present propose to import data
	if [ -r "$NAMESPACE/data/data.sql" ]; then
		if confirm "Would you like to import data from $NAMESPACE/data/data.sql?" ; then
			echo "Running: $sql$DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $NAMESPACE/data/data.sql"
			if [ $DEBUG -eq 0 ]; then
				$sql$DB_HOST$DB_PORT -U $DB_OWNER $password -d $DB_NAME -f $NAMESPACE/data/data.sql
				if [ $? -ne 0 ]; then
					die "an error occurs when importing file $NAMESPACE/data/data.sql."
				fi
			fi
		fi
	else
		# Import data directly from PostgreSQL
		if confirm "Would you like to import data from Oracle database directly into PostgreSQL?" ; then
			echo "Running: ora2pg$IMPORT_JOBS$PARALLEL_TABLES -c config/ora2pg.conf -t COPY --pg_dsn \"$pgdsn_defined\" --pg_user $DB_OWNER"
			if [ $DEBUG -eq 0 ]; then
				ora2pg$IMPORT_JOBS$PARALLEL_TABLES -c config/ora2pg.conf -t COPY --pg_dsn "$pgdsn_defined" --pg_user $DB_OWNER
				if [ $? -ne 0 ]; then
					die "an error occurs when importing data."
				fi
			fi
		fi
	fi

	if [ $NO_CONSTRAINTS -eq 0 ] && [ $IMPORT_DATA -eq 0 ]; then
		# Import indexes and constraint after data
		if [ $IMPORT_INDEXES_AFTER -eq 1 ]; then
			import_constraints
		fi
	fi
fi

exit 0

